# -*- coding: utf-8 -*-
"""
02. Pandas 练习题集合

本文件包含从基础到高级的Pandas练习题，每个练习都有详细的问题描述、
解决方案和测试用例。涵盖数据处理、分析和可视化的各个方面。

文件: 02_pandas_exercises.py
作者: Python学习展示项目
描述: 通过实际练习掌握Pandas的核心功能
"""

import pandas as pd
import numpy as np
import pytest
from typing import Tuple, List, Dict, Union
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

# ============================================================================
# 基础练习题 (Level 1: Beginner)
# ============================================================================

class BasicPandasExercises:
    """基础Pandas练习题"""
    
    @staticmethod
    def exercise_1_create_dataframes():
        """
        练习1：创建DataFrame
        
        任务：
        1. 从字典创建一个包含学生信息的DataFrame
        2. 从列表创建一个DataFrame
        3. 创建一个带有自定义索引的DataFrame
        4. 创建一个空的DataFrame并添加列
        
        返回：tuple，包含四个DataFrame
        """
        # TODO: 实现你的解决方案
        
        # 1. 从字典创建DataFrame
        student_data = {
            'name': ['Alice', 'Bob', 'Charlie', 'Diana'],
            'age': [20, 21, 19, 22],
            'grade': ['A', 'B', 'A', 'C'],
            'score': [95, 87, 92, 78]
        }
        df_from_dict = pd.DataFrame(student_data)
        
        # 2. 从列表创建DataFrame
        list_data = [['Alice', 20, 'A'], ['Bob', 21, 'B'], ['Charlie', 19, 'A']]
        df_from_list = pd.DataFrame(list_data, columns=['name', 'age', 'grade'])
        
        # 3. 带自定义索引的DataFrame
        df_custom_index = pd.DataFrame(
            {'product': ['laptop', 'mouse', 'keyboard'],
             'price': [1000, 25, 75]},
            index=['item1', 'item2', 'item3']
        )
        
        # 4. 空DataFrame并添加列
        df_empty = pd.DataFrame()
        df_empty['column1'] = [1, 2, 3]
        df_empty['column2'] = ['a', 'b', 'c']
        
        return df_from_dict, df_from_list, df_custom_index, df_empty
    
    @staticmethod
    def exercise_2_basic_operations():
        """
        练习2：基本操作
        
        任务：
        给定一个销售数据DataFrame
        1. 查看DataFrame的基本信息（形状、列名、数据类型）
        2. 选择特定的列
        3. 选择特定的行
        4. 添加新列
        
        返回：tuple，包含操作结果
        """
        # 创建示例数据
        sales_data = {
            'product': ['A', 'B', 'C', 'D', 'E'],
            'quantity': [10, 15, 8, 12, 20],
            'price': [100, 150, 80, 120, 200],
            'region': ['North', 'South', 'East', 'West', 'North']
        }
        df = pd.DataFrame(sales_data)
        
        # TODO: 实现你的解决方案
        
        # 1. 基本信息
        shape = df.shape
        columns = df.columns.tolist()
        dtypes = df.dtypes
        
        # 2. 选择列
        product_column = df['product']
        multiple_columns = df[['product', 'price']]
        
        # 3. 选择行
        first_three_rows = df.head(3)
        specific_rows = df.iloc[1:4]
        
        # 4. 添加新列
        df_with_total = df.copy()
        df_with_total['total_value'] = df_with_total['quantity'] * df_with_total['price']
        
        return (shape, columns, dtypes, product_column, multiple_columns, 
                first_three_rows, specific_rows, df_with_total)
    
    @staticmethod
    def exercise_3_filtering_sorting():
        """
        练习3：过滤和排序
        
        任务：
        使用员工数据进行过滤和排序操作
        1. 筛选年龄大于25的员工
        2. 筛选工资在50000-80000之间的员工
        3. 按工资降序排序
        4. 按部门和工资进行多级排序
        
        返回：tuple，包含过滤和排序结果
        """
        # 创建员工数据
        employee_data = {
            'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank'],
            'age': [25, 30, 35, 28, 32, 27],
            'salary': [50000, 75000, 90000, 60000, 85000, 55000],
            'department': ['IT', 'HR', 'IT', 'Finance', 'IT', 'HR']
        }
        df = pd.DataFrame(employee_data)
        
        # TODO: 实现你的解决方案
        
        # 1. 年龄大于25
        age_filter = df[df['age'] > 25]
        
        # 2. 工资在50000-80000之间
        salary_filter = df[(df['salary'] >= 50000) & (df['salary'] <= 80000)]
        
        # 3. 按工资降序排序
        salary_sorted = df.sort_values('salary', ascending=False)
        
        # 4. 多级排序
        multi_sorted = df.sort_values(['department', 'salary'], ascending=[True, False])
        
        return age_filter, salary_filter, salary_sorted, multi_sorted
    
    @staticmethod
    def exercise_4_groupby_aggregation():
        """
        练习4：分组和聚合
        
        任务：
        使用销售数据进行分组分析
        1. 按地区分组，计算总销售额
        2. 按产品分组，计算平均价格
        3. 按地区和产品分组，计算多个统计量
        4. 使用agg函数进行自定义聚合
        
        返回：tuple，包含分组结果
        """
        # 创建销售数据
        sales_data = {
            'product': ['A', 'B', 'A', 'C', 'B', 'A', 'C', 'B'],
            'region': ['North', 'South', 'North', 'East', 'South', 'East', 'North', 'East'],
            'quantity': [10, 15, 8, 12, 20, 5, 18, 25],
            'price': [100, 150, 100, 80, 150, 100, 80, 150],
            'cost': [70, 100, 70, 50, 100, 70, 50, 100]
        }
        df = pd.DataFrame(sales_data)
        df['revenue'] = df['quantity'] * df['price']
        
        # TODO: 实现你的解决方案
        
        # 1. 按地区分组，计算总销售额
        region_revenue = df.groupby('region')['revenue'].sum()
        
        # 2. 按产品分组，计算平均价格
        product_avg_price = df.groupby('product')['price'].mean()
        
        # 3. 按地区和产品分组，计算多个统计量
        multi_group_stats = df.groupby(['region', 'product']).agg({
            'quantity': 'sum',
            'revenue': 'sum',
            'price': 'mean'
        })
        
        # 4. 自定义聚合
        custom_agg = df.groupby('region').agg({
            'revenue': ['sum', 'mean', 'count'],
            'quantity': ['min', 'max']
        })
        
        return region_revenue, product_avg_price, multi_group_stats, custom_agg

# ============================================================================
# 中级练习题 (Level 2: Intermediate)
# ============================================================================

class IntermediatePandasExercises:
    """中级Pandas练习题"""
    
    @staticmethod
    def exercise_5_data_cleaning():
        """
        练习5：数据清洗
        
        任务：
        处理包含缺失值和异常值的数据
        1. 识别和处理缺失值
        2. 检测和处理重复数据
        3. 处理异常值
        4. 数据类型转换
        
        返回：tuple，包含清洗后的数据
        """
        # 创建包含问题的数据
        dirty_data = {
            'id': [1, 2, 3, 4, 5, 5, 7, 8, 9, 10],
            'name': ['Alice', 'Bob', None, 'Diana', 'Eve', 'Eve', 'Frank', 'Grace', 'Henry', 'Ivy'],
            'age': [25, 30, 35, -5, 32, 32, 27, 150, 29, 31],
            'salary': ['50000', '75000', '90000', '60000', None, '85000', '55000', '70000', '65000', '80000'],
            'join_date': ['2020-01-15', '2019-03-20', '2021-06-10', '2020-11-05', 
                         '2022-02-28', '2022-02-28', 'invalid_date', '2021-09-15', '2020-07-30', '2019-12-01']
        }
        df = pd.DataFrame(dirty_data)
        
        # TODO: 实现你的解决方案
        
        # 1. 处理缺失值
        # 检查缺失值
        missing_info = df.isnull().sum()
        
        # 填充缺失的姓名
        df_cleaned = df.copy()
        df_cleaned['name'].fillna('Unknown', inplace=True)
        
        # 填充缺失的工资（用中位数）
        df_cleaned['salary'] = pd.to_numeric(df_cleaned['salary'], errors='coerce')
        median_salary = df_cleaned['salary'].median()
        df_cleaned['salary'].fillna(median_salary, inplace=True)
        
        # 2. 处理重复数据
        duplicates_info = df_cleaned.duplicated().sum()
        df_no_duplicates = df_cleaned.drop_duplicates()
        
        # 3. 处理异常值（年龄）
        # 将不合理的年龄设为NaN，然后填充
        df_no_duplicates.loc[(df_no_duplicates['age'] < 0) | (df_no_duplicates['age'] > 100), 'age'] = np.nan
        df_no_duplicates['age'].fillna(df_no_duplicates['age'].median(), inplace=True)
        
        # 4. 数据类型转换
        # 转换日期列
        df_no_duplicates['join_date'] = pd.to_datetime(df_no_duplicates['join_date'], errors='coerce')
        
        # 填充无效日期
        df_no_duplicates['join_date'].fillna(pd.to_datetime('2020-01-01'), inplace=True)
        
        return missing_info, duplicates_info, df_cleaned, df_no_duplicates
    
    @staticmethod
    def exercise_6_merging_joining():
        """
        练习6：数据合并和连接
        
        任务：
        练习不同类型的数据合并
        1. 内连接（inner join）
        2. 左连接（left join）
        3. 外连接（outer join）
        4. 基于索引的连接
        
        返回：tuple，包含不同连接结果
        """
        # 创建两个相关的DataFrame
        employees = pd.DataFrame({
            'emp_id': [1, 2, 3, 4, 5],
            'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
            'dept_id': [10, 20, 10, 30, 20]
        })
        
        departments = pd.DataFrame({
            'dept_id': [10, 20, 30, 40],
            'dept_name': ['IT', 'HR', 'Finance', 'Marketing'],
            'budget': [100000, 80000, 120000, 90000]
        })
        
        # TODO: 实现你的解决方案
        
        # 1. 内连接
        inner_join = pd.merge(employees, departments, on='dept_id', how='inner')
        
        # 2. 左连接
        left_join = pd.merge(employees, departments, on='dept_id', how='left')
        
        # 3. 外连接
        outer_join = pd.merge(employees, departments, on='dept_id', how='outer')
        
        # 4. 基于索引的连接
        emp_indexed = employees.set_index('emp_id')
        dept_indexed = departments.set_index('dept_id')
        index_join = emp_indexed.join(dept_indexed, on='dept_id')
        
        return inner_join, left_join, outer_join, index_join
    
    @staticmethod
    def exercise_7_time_series():
        """
        练习7：时间序列分析
        
        任务：
        处理时间序列数据
        1. 创建时间序列数据
        2. 重采样（按月汇总）
        3. 计算移动平均
        4. 处理时间索引
        
        返回：tuple，包含时间序列分析结果
        """
        # TODO: 实现你的解决方案
        
        # 1. 创建时间序列数据
        dates = pd.date_range('2023-01-01', periods=365, freq='D')
        np.random.seed(42)
        values = np.random.randn(365).cumsum() + 100
        ts_data = pd.Series(values, index=dates, name='value')
        
        # 2. 重采样（按月汇总）
        monthly_sum = ts_data.resample('M').sum()
        monthly_mean = ts_data.resample('M').mean()
        
        # 3. 计算移动平均
        rolling_7d = ts_data.rolling(window=7).mean()
        rolling_30d = ts_data.rolling(window=30).mean()
        
        # 4. 时间索引操作
        # 选择特定月份的数据
        january_data = ts_data['2023-01']
        
        # 选择特定日期范围
        q1_data = ts_data['2023-01-01':'2023-03-31']
        
        return ts_data, monthly_sum, monthly_mean, rolling_7d, rolling_30d, january_data, q1_data
    
    @staticmethod
    def exercise_8_pivot_tables():
        """
        练习8：透视表和交叉表
        
        任务：
        使用销售数据创建透视表
        1. 基本透视表
        2. 多级透视表
        3. 交叉表
        4. 透视表的样式设置
        
        返回：tuple，包含透视表结果
        """
        # 创建销售数据
        sales_data = {
            'date': pd.date_range('2023-01-01', periods=100, freq='D'),
            'salesperson': np.random.choice(['Alice', 'Bob', 'Charlie'], 100),
            'region': np.random.choice(['North', 'South', 'East', 'West'], 100),
            'product': np.random.choice(['A', 'B', 'C'], 100),
            'quantity': np.random.randint(1, 20, 100),
            'price': np.random.choice([10, 15, 20, 25], 100)
        }
        df = pd.DataFrame(sales_data)
        df['revenue'] = df['quantity'] * df['price']
        df['month'] = df['date'].dt.month
        
        # TODO: 实现你的解决方案
        
        # 1. 基本透视表
        basic_pivot = pd.pivot_table(df, values='revenue', index='region', 
                                   columns='product', aggfunc='sum', fill_value=0)
        
        # 2. 多级透视表
        multi_pivot = pd.pivot_table(df, values=['revenue', 'quantity'], 
                                   index=['region', 'salesperson'], 
                                   columns='product', aggfunc='sum', fill_value=0)
        
        # 3. 交叉表
        crosstab = pd.crosstab(df['region'], df['product'], values=df['revenue'], 
                              aggfunc='sum', margins=True)
        
        # 4. 按月份的透视表
        monthly_pivot = pd.pivot_table(df, values='revenue', index='month', 
                                     columns='region', aggfunc='sum', fill_value=0)
        
        return basic_pivot, multi_pivot, crosstab, monthly_pivot

# ============================================================================
# 高级练习题 (Level 3: Advanced)
# ============================================================================

class AdvancedPandasExercises:
    """高级Pandas练习题"""
    
    @staticmethod
    def exercise_9_advanced_groupby():
        """
        练习9：高级分组操作
        
        任务：
        使用复杂的分组操作
        1. 自定义聚合函数
        2. 分组后的变换
        3. 分组后的过滤
        4. 多级分组的复杂操作
        
        返回：tuple，包含高级分组结果
        """
        # 创建复杂的销售数据
        np.random.seed(42)
        data = {
            'store_id': np.random.choice(['S001', 'S002', 'S003'], 200),
            'product_category': np.random.choice(['Electronics', 'Clothing', 'Food'], 200),
            'product_name': np.random.choice(['Product_A', 'Product_B', 'Product_C', 'Product_D'], 200),
            'sale_date': pd.date_range('2023-01-01', periods=200, freq='D'),
            'quantity': np.random.randint(1, 50, 200),
            'unit_price': np.random.uniform(10, 100, 200),
            'customer_age': np.random.randint(18, 70, 200)
        }
        df = pd.DataFrame(data)
        df['total_sales'] = df['quantity'] * df['unit_price']
        df['month'] = df['sale_date'].dt.month
        
        # TODO: 实现你的解决方案
        
        # 1. 自定义聚合函数
        def sales_summary(series):
            return pd.Series({
                'total': series.sum(),
                'average': series.mean(),
                'count': series.count(),
                'std': series.std()
            })
        
        custom_agg = df.groupby('store_id')['total_sales'].apply(sales_summary)
        
        # 2. 分组后的变换（计算每个商店内的销售额占比）
        df['sales_pct_in_store'] = df.groupby('store_id')['total_sales'].transform(
            lambda x: x / x.sum() * 100
        )
        
        # 3. 分组后的过滤（只保留平均销售额大于1000的商店）
        filtered_stores = df.groupby('store_id').filter(
            lambda x: x['total_sales'].mean() > 1000
        )
        
        # 4. 多级分组的复杂操作
        multi_level_analysis = df.groupby(['store_id', 'product_category']).agg({
            'total_sales': ['sum', 'mean', 'count'],
            'quantity': ['sum', 'mean'],
            'customer_age': ['mean', 'median']
        }).round(2)
        
        return custom_agg, df[['store_id', 'total_sales', 'sales_pct_in_store']], filtered_stores, multi_level_analysis
    
    @staticmethod
    def exercise_10_window_functions():
        """
        练习10：窗口函数
        
        任务：
        使用窗口函数进行高级分析
        1. 滚动窗口计算
        2. 扩展窗口计算
        3. 分组内的窗口函数
        4. 自定义窗口函数
        
        返回：tuple，包含窗口函数结果
        """
        # 创建股票价格数据
        np.random.seed(42)
        dates = pd.date_range('2023-01-01', periods=100, freq='D')
        stocks = ['AAPL', 'GOOGL', 'MSFT']
        
        stock_data = []
        for stock in stocks:
            prices = 100 + np.random.randn(100).cumsum()
            for i, (date, price) in enumerate(zip(dates, prices)):
                stock_data.append({
                    'date': date,
                    'stock': stock,
                    'price': max(price, 10),  # 确保价格为正
                    'volume': np.random.randint(1000, 10000)
                })
        
        df = pd.DataFrame(stock_data)
        
        # TODO: 实现你的解决方案
        
        # 1. 滚动窗口计算（移动平均）
        df['price_ma_7'] = df.groupby('stock')['price'].rolling(window=7).mean().reset_index(0, drop=True)
        df['price_ma_20'] = df.groupby('stock')['price'].rolling(window=20).mean().reset_index(0, drop=True)
        
        # 2. 扩展窗口计算（累积统计）
        df['price_expanding_mean'] = df.groupby('stock')['price'].expanding().mean().reset_index(0, drop=True)
        df['price_expanding_std'] = df.groupby('stock')['price'].expanding().std().reset_index(0, drop=True)
        
        # 3. 分组内的窗口函数（排名）
        df['price_rank'] = df.groupby('stock')['price'].rank(method='dense')
        df['volume_pct_rank'] = df.groupby('stock')['volume'].rank(pct=True)
        
        # 4. 自定义窗口函数（价格变化率）
        def price_change_rate(series):
            if len(series) < 2:
                return np.nan
            return (series.iloc[-1] - series.iloc[0]) / series.iloc[0] * 100
        
        df['price_change_7d'] = df.groupby('stock')['price'].rolling(window=7).apply(price_change_rate).reset_index(0, drop=True)
        
        return df[['date', 'stock', 'price', 'price_ma_7', 'price_ma_20']].head(20), \
               df[['stock', 'price_expanding_mean', 'price_expanding_std']].head(10), \
               df[['stock', 'price', 'price_rank', 'volume_pct_rank']].head(15), \
               df[['stock', 'price', 'price_change_7d']].dropna().head(10)
    
    @staticmethod
    def exercise_11_text_processing():
        """
        练习11：文本数据处理
        
        任务：
        处理文本数据
        1. 字符串基本操作
        2. 正则表达式匹配
        3. 文本分割和提取
        4. 文本清洗和标准化
        
        返回：tuple，包含文本处理结果
        """
        # 创建包含文本的数据
        text_data = {
            'customer_id': [1, 2, 3, 4, 5, 6, 7, 8],
            'name': ['John Doe', 'jane smith', 'BOB JOHNSON', 'Alice Brown', 
                    'Charlie Davis', 'Eva Wilson', 'Frank Miller', 'Grace Lee'],
            'email': ['john.doe@email.com', 'jane@gmail.com', 'bob.j@yahoo.com', 
                     'alice.brown@company.co.uk', 'charlie@hotmail.com', 
                     'eva.wilson@email.org', 'frank.miller@business.net', 'grace@email.com'],
            'phone': ['123-456-7890', '(555) 123-4567', '555.987.6543', 
                     '+1-800-555-0123', '9876543210', '555-0000', 
                     '(123)456-7890', '555 123 4567'],
            'address': ['123 Main St, New York, NY 10001', 
                       '456 Oak Ave, Los Angeles, CA 90210',
                       '789 Pine Rd, Chicago, IL 60601',
                       '321 Elm St, Houston, TX 77001',
                       '654 Maple Dr, Phoenix, AZ 85001',
                       '987 Cedar Ln, Philadelphia, PA 19101',
                       '147 Birch Way, San Antonio, TX 78201',
                       '258 Spruce Ct, San Diego, CA 92101']
        }
        df = pd.DataFrame(text_data)
        
        # TODO: 实现你的解决方案
        
        # 1. 字符串基本操作
        df['name_upper'] = df['name'].str.upper()
        df['name_title'] = df['name'].str.title()
        df['name_length'] = df['name'].str.len()
        
        # 2. 正则表达式匹配
        # 提取邮箱域名
        df['email_domain'] = df['email'].str.extract(r'@([^.]+\.[a-z]+)')
        
        # 检查是否为Gmail
        df['is_gmail'] = df['email'].str.contains('gmail', case=False)
        
        # 3. 文本分割和提取
        # 分割姓名
        name_split = df['name'].str.split(' ', expand=True)
        df['first_name'] = name_split[0]
        df['last_name'] = name_split[1]
        
        # 提取州代码
        df['state'] = df['address'].str.extract(r', ([A-Z]{2}) ')
        
        # 4. 文本清洗和标准化
        # 清洗电话号码（只保留数字）
        df['phone_clean'] = df['phone'].str.replace(r'[^0-9]', '', regex=True)
        
        # 标准化电话号码格式
        def format_phone(phone):
            if len(phone) == 10:
                return f"({phone[:3]}) {phone[3:6]}-{phone[6:]}"
            elif len(phone) == 11 and phone[0] == '1':
                return f"({phone[1:4]}) {phone[4:7]}-{phone[7:]}"
            else:
                return phone
        
        df['phone_formatted'] = df['phone_clean'].apply(format_phone)
        
        return df[['name', 'name_upper', 'name_title', 'name_length']], \
               df[['email', 'email_domain', 'is_gmail']], \
               df[['name', 'first_name', 'last_name', 'address', 'state']], \
               df[['phone', 'phone_clean', 'phone_formatted']]
    
    @staticmethod
    def exercise_12_performance_optimization():
        """
        练习12：性能优化
        
        任务：
        比较不同方法的性能
        1. 向量化操作 vs 循环
        2. 使用category数据类型
        3. 内存优化
        4. 查询优化
        
        返回：tuple，包含性能比较结果
        """
        import time
        
        # 创建大型数据集
        np.random.seed(42)
        n = 100000
        large_df = pd.DataFrame({
            'id': range(n),
            'category': np.random.choice(['A', 'B', 'C', 'D', 'E'], n),
            'value1': np.random.randn(n),
            'value2': np.random.randn(n),
            'date': pd.date_range('2020-01-01', periods=n, freq='H')
        })
        
        # TODO: 实现你的解决方案
        
        # 1. 向量化操作 vs 循环
        # 向量化方法
        start_time = time.time()
        vectorized_result = large_df['value1'] * large_df['value2'] + 10
        vectorized_time = time.time() - start_time
        
        # 循环方法（仅用于演示，实际中避免使用）
        start_time = time.time()
        loop_result = []
        for i in range(min(1000, len(large_df))):  # 只测试前1000行
            loop_result.append(large_df.iloc[i]['value1'] * large_df.iloc[i]['value2'] + 10)
        loop_time = time.time() - start_time
        
        # 2. 使用category数据类型
        memory_before = large_df.memory_usage(deep=True).sum()
        
        optimized_df = large_df.copy()
        optimized_df['category'] = optimized_df['category'].astype('category')
        
        memory_after = optimized_df.memory_usage(deep=True).sum()
        memory_saved = memory_before - memory_after
        
        # 3. 查询优化示例
        # 使用query方法
        start_time = time.time()
        query_result = large_df.query('value1 > 0 and category == "A"')
        query_time = time.time() - start_time
        
        # 使用布尔索引
        start_time = time.time()
        boolean_result = large_df[(large_df['value1'] > 0) & (large_df['category'] == 'A')]
        boolean_time = time.time() - start_time
        
        performance_summary = {
            'vectorized_time': vectorized_time,
            'loop_time_per_1000': loop_time,
            'memory_before_MB': memory_before / 1024 / 1024,
            'memory_after_MB': memory_after / 1024 / 1024,
            'memory_saved_MB': memory_saved / 1024 / 1024,
            'query_time': query_time,
            'boolean_time': boolean_time
        }
        
        return performance_summary, optimized_df.dtypes, query_result.shape, boolean_result.shape

# ============================================================================
# 实战练习题 (Level 4: Real-world Applications)
# ============================================================================

class RealWorldPandasExercises:
    """实战Pandas练习题"""
    
    @staticmethod
    def exercise_13_ecommerce_analysis():
        """
        练习13：电商数据分析
        
        任务：
        分析电商平台的销售数据
        1. 客户行为分析
        2. 产品销售分析
        3. 时间趋势分析
        4. RFM分析（最近性、频率、货币价值）
        
        返回：tuple，包含分析结果
        """
        # 生成模拟电商数据
        np.random.seed(42)
        n_customers = 1000
        n_orders = 5000
        
        # 客户数据
        customers = pd.DataFrame({
            'customer_id': range(1, n_customers + 1),
            'registration_date': pd.date_range('2022-01-01', periods=n_customers, freq='D'),
            'age': np.random.randint(18, 70, n_customers),
            'gender': np.random.choice(['M', 'F'], n_customers),
            'city': np.random.choice(['New York', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix'], n_customers)
        })
        
        # 订单数据
        orders = pd.DataFrame({
            'order_id': range(1, n_orders + 1),
            'customer_id': np.random.choice(customers['customer_id'], n_orders),
            'order_date': pd.date_range('2022-01-01', periods=n_orders, freq='H'),
            'product_category': np.random.choice(['Electronics', 'Clothing', 'Books', 'Home', 'Sports'], n_orders),
            'product_price': np.random.uniform(10, 500, n_orders),
            'quantity': np.random.randint(1, 5, n_orders),
            'discount': np.random.uniform(0, 0.3, n_orders)
        })
        
        orders['total_amount'] = orders['product_price'] * orders['quantity'] * (1 - orders['discount'])
        
        # TODO: 实现你的解决方案
        
        # 1. 客户行为分析
        customer_behavior = orders.groupby('customer_id').agg({
            'order_id': 'count',
            'total_amount': ['sum', 'mean'],
            'order_date': ['min', 'max']
        }).round(2)
        
        customer_behavior.columns = ['order_count', 'total_spent', 'avg_order_value', 'first_order', 'last_order']
        
        # 2. 产品销售分析
        product_analysis = orders.groupby('product_category').agg({
            'total_amount': ['sum', 'mean', 'count'],
            'quantity': 'sum',
            'discount': 'mean'
        }).round(2)
        
        # 3. 时间趋势分析
        orders['order_month'] = orders['order_date'].dt.to_period('M')
        monthly_trends = orders.groupby('order_month').agg({
            'total_amount': 'sum',
            'order_id': 'count',
            'customer_id': 'nunique'
        })
        
        # 4. RFM分析
        analysis_date = orders['order_date'].max()
        
        rfm = orders.groupby('customer_id').agg({
            'order_date': lambda x: (analysis_date - x.max()).days,  # Recency
            'order_id': 'count',  # Frequency
            'total_amount': 'sum'  # Monetary
        })
        
        rfm.columns = ['recency', 'frequency', 'monetary']
        
        # RFM评分
        rfm['r_score'] = pd.qcut(rfm['recency'], 5, labels=[5, 4, 3, 2, 1])
        rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 5, labels=[1, 2, 3, 4, 5])
        rfm['m_score'] = pd.qcut(rfm['monetary'], 5, labels=[1, 2, 3, 4, 5])
        
        rfm['rfm_score'] = rfm['r_score'].astype(str) + rfm['f_score'].astype(str) + rfm['m_score'].astype(str)
        
        return customer_behavior.head(10), product_analysis, monthly_trends, rfm.head(10)
    
    @staticmethod
    def exercise_14_financial_analysis():
        """
        练习14：金融数据分析
        
        任务：
        分析股票和投资组合数据
        1. 计算收益率和风险指标
        2. 投资组合分析
        3. 相关性分析
        4. 风险价值(VaR)计算
        
        返回：tuple，包含金融分析结果
        """
        # 生成模拟股票数据
        np.random.seed(42)
        dates = pd.date_range('2022-01-01', '2023-12-31', freq='D')
        stocks = ['AAPL', 'GOOGL', 'MSFT', 'AMZN', 'TSLA']
        
        stock_data = {}
        for stock in stocks:
            # 生成股票价格（使用几何布朗运动）
            returns = np.random.normal(0.001, 0.02, len(dates))
            prices = [100]
            for ret in returns:
                prices.append(prices[-1] * (1 + ret))
            stock_data[stock] = prices[1:]
        
        stock_prices = pd.DataFrame(stock_data, index=dates)
        
        # TODO: 实现你的解决方案
        
        # 1. 计算收益率和风险指标
        daily_returns = stock_prices.pct_change().dropna()
        
        risk_metrics = pd.DataFrame({
            'annual_return': daily_returns.mean() * 252,
            'annual_volatility': daily_returns.std() * np.sqrt(252),
            'sharpe_ratio': (daily_returns.mean() * 252) / (daily_returns.std() * np.sqrt(252)),
            'max_drawdown': ((stock_prices / stock_prices.expanding().max() - 1).min())
        }).round(4)
        
        # 2. 投资组合分析（等权重投资组合）
        portfolio_weights = np.array([0.2, 0.2, 0.2, 0.2, 0.2])
        portfolio_returns = (daily_returns * portfolio_weights).sum(axis=1)
        
        portfolio_metrics = {
            'annual_return': portfolio_returns.mean() * 252,
            'annual_volatility': portfolio_returns.std() * np.sqrt(252),
            'sharpe_ratio': (portfolio_returns.mean() * 252) / (portfolio_returns.std() * np.sqrt(252)),
            'cumulative_return': (1 + portfolio_returns).cumprod().iloc[-1] - 1
        }
        
        # 3. 相关性分析
        correlation_matrix = daily_returns.corr()
        
        # 4. 风险价值(VaR)计算（95%置信度）
        var_95 = daily_returns.quantile(0.05)
        portfolio_var_95 = portfolio_returns.quantile(0.05)
        
        return risk_metrics, portfolio_metrics, correlation_matrix, var_95, portfolio_var_95
    
    @staticmethod
    def exercise_15_student_performance_analysis():
        """
        练习15：学生成绩分析
        
        任务：
        分析学生成绩数据
        1. 成绩统计分析
        2. 科目相关性分析
        3. 学生分类和排名
        4. 成绩预测模型准备
        
        返回：tuple，包含学生成绩分析结果
        """
        # 生成模拟学生数据
        np.random.seed(42)
        n_students = 500
        
        students = pd.DataFrame({
            'student_id': range(1, n_students + 1),
            'name': [f'Student_{i}' for i in range(1, n_students + 1)],
            'grade': np.random.choice([9, 10, 11, 12], n_students),
            'gender': np.random.choice(['M', 'F'], n_students),
            'math_score': np.random.normal(75, 15, n_students),
            'english_score': np.random.normal(78, 12, n_students),
            'science_score': np.random.normal(73, 14, n_students),
            'history_score': np.random.normal(76, 13, n_students),
            'attendance_rate': np.random.uniform(0.7, 1.0, n_students),
            'study_hours_per_week': np.random.normal(20, 8, n_students)
        })
        
        # 确保分数在合理范围内
        score_columns = ['math_score', 'english_score', 'science_score', 'history_score']
        for col in score_columns:
            students[col] = np.clip(students[col], 0, 100)
        
        students['study_hours_per_week'] = np.clip(students['study_hours_per_week'], 0, 50)
        
        # TODO: 实现你的解决方案
        
        # 1. 成绩统计分析
        students['total_score'] = students[score_columns].sum(axis=1)
        students['average_score'] = students[score_columns].mean(axis=1)
        
        grade_stats = students.groupby('grade')[score_columns + ['average_score']].agg({
            'math_score': ['mean', 'std'],
            'english_score': ['mean', 'std'],
            'science_score': ['mean', 'std'],
            'history_score': ['mean', 'std'],
            'average_score': ['mean', 'std', 'min', 'max']
        }).round(2)
        
        # 2. 科目相关性分析
        subject_correlation = students[score_columns].corr()
        
        # 学习时间与成绩的相关性
        study_correlation = students[['study_hours_per_week', 'attendance_rate'] + score_columns].corr()
        
        # 3. 学生分类和排名
        # 成绩等级分类
        def classify_performance(avg_score):
            if avg_score >= 90:
                return 'Excellent'
            elif avg_score >= 80:
                return 'Good'
            elif avg_score >= 70:
                return 'Average'
            elif avg_score >= 60:
                return 'Below Average'
            else:
                return 'Poor'
        
        students['performance_level'] = students['average_score'].apply(classify_performance)
        
        # 排名
        students['overall_rank'] = students['average_score'].rank(method='dense', ascending=False)
        students['grade_rank'] = students.groupby('grade')['average_score'].rank(method='dense', ascending=False)
        
        performance_distribution = students['performance_level'].value_counts()
        
        # 4. 成绩预测模型准备（特征工程）
        # 创建一些有用的特征
        students['math_english_diff'] = students['math_score'] - students['english_score']
        students['science_history_diff'] = students['science_score'] - students['history_score']
        students['best_subject_score'] = students[score_columns].max(axis=1)
        students['worst_subject_score'] = students[score_columns].min(axis=1)
        students['score_variance'] = students[score_columns].var(axis=1)
        
        # 按年级和性别的分析
        demographic_analysis = students.groupby(['grade', 'gender']).agg({
            'average_score': ['mean', 'count'],
            'study_hours_per_week': 'mean',
            'attendance_rate': 'mean'
        }).round(2)
        
        return grade_stats, subject_correlation, performance_distribution, demographic_analysis, students[['student_id', 'average_score', 'performance_level', 'overall_rank', 'grade_rank']].head(20)

# ============================================================================
# 测试函数
# ============================================================================

def test_basic_exercises():
    """测试基础练习题"""
    print("测试基础练习题...")
    
    # 测试练习1
    df_dict, df_list, df_custom, df_empty = BasicPandasExercises.exercise_1_create_dataframes()
    assert isinstance(df_dict, pd.DataFrame)
    assert len(df_dict) == 4
    assert 'name' in df_dict.columns
    
    # 测试练习2
    results = BasicPandasExercises.exercise_2_basic_operations()
    assert len(results) == 8
    
    # 测试练习3
    age_filter, salary_filter, salary_sorted, multi_sorted = BasicPandasExercises.exercise_3_filtering_sorting()
    assert isinstance(age_filter, pd.DataFrame)
    assert len(age_filter) > 0
    
    # 测试练习4
    region_rev, product_avg, multi_stats, custom_agg = BasicPandasExercises.exercise_4_groupby_aggregation()
    assert isinstance(region_rev, pd.Series)
    assert isinstance(product_avg, pd.Series)
    
    print("✅ 基础练习题测试通过！")

def test_intermediate_exercises():
    """测试中级练习题"""
    print("测试中级练习题...")
    
    # 测试练习5
    missing_info, dup_info, df_cleaned, df_final = IntermediatePandasExercises.exercise_5_data_cleaning()
    assert isinstance(missing_info, pd.Series)
    assert isinstance(df_final, pd.DataFrame)
    
    # 测试练习6
    inner, left, outer, index_join = IntermediatePandasExercises.exercise_6_merging_joining()
    assert isinstance(inner, pd.DataFrame)
    assert len(inner) <= len(left)
    
    # 测试练习7
    ts, monthly_sum, monthly_mean, roll_7, roll_30, jan, q1 = IntermediatePandasExercises.exercise_7_time_series()
    assert isinstance(ts, pd.Series)
    assert len(monthly_sum) == 12  # 12个月
    
    # 测试练习8
    basic_pivot, multi_pivot, crosstab, monthly_pivot = IntermediatePandasExercises.exercise_8_pivot_tables()
    assert isinstance(basic_pivot, pd.DataFrame)
    assert isinstance(crosstab, pd.DataFrame)
    
    print("✅ 中级练习题测试通过！")

def test_advanced_exercises():
    """测试高级练习题"""
    print("测试高级练习题...")
    
    # 测试练习9
    custom_agg, sales_pct, filtered, multi_analysis = AdvancedPandasExercises.exercise_9_advanced_groupby()
    assert isinstance(custom_agg, pd.DataFrame)
    assert 'sales_pct_in_store' in sales_pct.columns
    
    # 测试练习10
    ma_data, expanding_data, rank_data, change_data = AdvancedPandasExercises.exercise_10_window_functions()
    assert isinstance(ma_data, pd.DataFrame)
    assert 'price_ma_7' in ma_data.columns
    
    # 测试练习11
    name_ops, email_ops, split_ops, phone_ops = AdvancedPandasExercises.exercise_11_text_processing()
    assert isinstance(name_ops, pd.DataFrame)
    assert 'name_upper' in name_ops.columns
    
    # 测试练习12
    perf_summary, dtypes, query_shape, bool_shape = AdvancedPandasExercises.exercise_12_performance_optimization()
    assert isinstance(perf_summary, dict)
    assert 'vectorized_time' in perf_summary
    
    print("✅ 高级练习题测试通过！")

def test_real_world_exercises():
    """测试实战练习题"""
    print("测试实战练习题...")
    
    # 测试练习13
    customer_behavior, product_analysis, monthly_trends, rfm = RealWorldPandasExercises.exercise_13_ecommerce_analysis()
    assert isinstance(customer_behavior, pd.DataFrame)
    assert 'rfm_score' in rfm.columns
    
    # 测试练习14
    risk_metrics, portfolio_metrics, corr_matrix, var_95, portfolio_var = RealWorldPandasExercises.exercise_14_financial_analysis()
    assert isinstance(risk_metrics, pd.DataFrame)
    assert 'sharpe_ratio' in risk_metrics.columns
    
    # 测试练习15
    grade_stats, subj_corr, perf_dist, demo_analysis, top_students = RealWorldPandasExercises.exercise_15_student_performance_analysis()
    assert isinstance(grade_stats, pd.DataFrame)
    assert isinstance(perf_dist, pd.Series)
    
    print("✅ 实战练习题测试通过！")

def run_all_tests():
    """运行所有测试"""
    print("=" * 60)
    print("Pandas 练习题测试")
    print("=" * 60)
    
    test_basic_exercises()
    test_intermediate_exercises()
    test_advanced_exercises()
    test_real_world_exercises()
    
    print("\n" + "=" * 60)
    print("🎉 所有Pandas练习题测试通过！")
    print("=" * 60)

def demonstrate_exercises():
    """演示练习题的解决方案"""
    print("Pandas 练习题演示")
    print("=" * 80)
    
    print("\n📚 基础练习题演示:")
    print("-" * 40)
    
    # 演示练习1
    print("\n练习1: 创建DataFrame")
    df_dict, df_list, df_custom, df_empty = BasicPandasExercises.exercise_1_create_dataframes()
    print("从字典创建的DataFrame:")
    print(df_dict.head())
    
    # 演示练习13（电商分析）
    print("\n🛒 实战练习演示: 电商数据分析")
    print("-" * 40)
    customer_behavior, product_analysis, monthly_trends, rfm = RealWorldPandasExercises.exercise_13_ecommerce_analysis()
    print("客户行为分析（前5名）:")
    print(customer_behavior.head())
    print("\n产品销售分析:")
    print(product_analysis)
    
    # 演示练习15（学生成绩分析）
    print("\n📊 学生成绩分析演示:")
    print("-" * 40)
    grade_stats, subj_corr, perf_dist, demo_analysis, top_students = RealWorldPandasExercises.exercise_15_student_performance_analysis()
    print("成绩等级分布:")
    print(perf_dist)
    print("\n前20名学生:")
    print(top_students)

if __name__ == "__main__":
    demonstrate_exercises()
    print("\n")
    run_all_tests()